// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial
{
	internal static class GeometryColumnsTable
	{
	
		internal static bool CheckGeometryColumnExists(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			return DatabaseHelper.CheckQueryExists(command, string.Format(
@"SELECT [F_GEOMETRY_COLUMN]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2"
)
, schemaName
, tableName
, columnName
);
		}
	
		internal static void ExecuteDeleteMetaRecord(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			DatabaseHelper.BuildCommand(command, 
@"DELETE FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2"
, schemaName
, tableName
, columnName
);
			command.ExecuteNonQuery();
		}
	
		internal static void ExecuteAddMetaRecord(SqlCommand command, string schemaName, string tableName, string columnName, int srid, string type)
		{
			ExecuteDeleteMetaRecord(command, schemaName, tableName, columnName);
	
			DatabaseHelper.BuildCommand(command, string.Format(
@"INSERT INTO [ST].[GEOMETRY_COLUMNS]
([F_TABLE_CATALOG], [F_TABLE_SCHEMA], [F_TABLE_NAME], [F_GEOMETRY_COLUMN], [COORD_DIMENSION], [SRID], [GEOMETRY_TYPE])
VALUES ('', '{0}','{1}','{2}',{3},{4},'{5}')"
, schemaName, tableName, columnName, 2, srid, type));
			command.ExecuteNonQuery();
		}
		
		internal static void ExecuteCreate(SqlCommand command)
		{
			if (!DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
			{
				command.CommandText =
@"CREATE TABLE [ST].[GEOMETRY_COLUMNS](
[F_TABLE_CATALOG] [varchar](256) NOT NULL,
[F_TABLE_SCHEMA] [varchar](256) NOT NULL,
[F_TABLE_NAME] [varchar](256) NOT NULL,
[F_GEOMETRY_COLUMN] [varchar](256) NOT NULL,
[COORD_DIMENSION] [int] NOT NULL,
[SRID] [int] NOT NULL,
[GEOMETRY_TYPE] [varchar](30) NOT NULL
)";
				command.ExecuteNonQuery();
			}
		}

		internal static void ExecuteBackup(SqlCommand command)
		{
			if (DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
			{
				string backupTableName = "ST_GEOMETRY_COLUMNS_BACKUP_" + DateTime.Now.ToString("yyyyMMdd_HHmm");
				command.CommandText = "SELECT * INTO " + backupTableName + " FROM [ST].[GEOMETRY_COLUMNS]";
				command.ExecuteNonQuery();
			}
		}
	
		internal static void ExecuteDeleteMetaRecords(SqlCommand command, string schemaName, string tableName)
		{
			DatabaseHelper.BuildCommand(command, 
@"DELETE FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0 
AND [F_TABLE_NAME] = @p1"
, schemaName
, tableName
);
			command.ExecuteNonQuery();
		}
		
		internal static List<string> GetGeometryColumnsByTable(SqlCommand command, string schemaName, string tableName)
		{
			List<string> columns = new List<string>();
			DatabaseHelper.BuildCommand(command, 
@"SELECT [F_GEOMETRY_COLUMN]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1"
, schemaName
, tableName
);
			using (SqlDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
					columns.Add(reader.GetString(0));
			}
			return columns;
		}

		internal static int GetSRID(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			List<string> columns = new List<string>();
			DatabaseHelper.BuildCommand(command,
@"SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
"
, schemaName
, tableName
, columnName
);
			using (SqlDataReader reader = command.ExecuteReader())
			{
				if (reader.Read())
					return reader.GetInt32(0);
			}
			return -1;
		}
	
	}
}
